// .........................................................................
// Title: amounts_outstanding.do
//
// Builds yearly panel of bond amounts oustanding by combining data from
// various sources (Factset, Dealogic, S&P Capital IQ)
// .........................................................................

* ---------------------------------------
* Merge amounts outstanding from sources
* ---------------------------------------

* values outstanding from factset dcs
use "$tmp/dcs_os_amounts_y", clear
drop if missing(cusip)
bys cusip year: gen N = _N
assert N == 1
drop N
save "$tmp/factset_values_outstanding", replace

* values offered from dealogic dcm
use "$tmp/dealogic_dcm_issuance_cleaned.dta", clear
keep cusip value
drop if missing(cusip)
bys cusip: keep if _n == 1
rename value value_offered
save "$tmp/dealogic_values_issued", replace

* merge value outstanding data and harmonize, at yearly frequency
use "$tmp/factset_values_outstanding", clear
mmerge cusip year using "$tmp/ciq_clean_amounts", unmatched(m) uname(iq_)
mmerge cusip using "$tmp/dealogic_values_issued", unmatched(m) uname(dlg_)
drop _merge
rename os_amount value_outstanding
replace iq_amount_outstanding = . if iq_amount_outstanding == 0
replace dlg_value_offered = . if dlg_value_offered == 0
replace value_outstanding = . if value_outstanding == 0
replace value_outstanding = iq_amount_outstanding if missing(value_outstanding)
replace value_outstanding = dlg_value_offered if missing(value_outstanding)
keep cusip year value_outstanding
replace value_outstanding = . if value_outstanding > 50000 // anything above 50B is a clear mistake in the data

* fill in yearly panel
drop if missing(cusip)
egen _cusip = group(cusip)
gsort _cusip year
xtset _cusip year
tsfill
by _cusip: carryforward value_outstanding, replace
drop _cusip
gsort cusip year
save "$tmp/amounts_outstanding_y", replace

* convert amounts to market value using trace
use "$tmp/amounts_outstanding_y", clear
mmerge cusip year using "$tmp/trace_clean_prices_y.dta", unmatched(m)
gen _in_trace = (_merge == 3)
drop _merge
gen value_outstanding_mv = value_outstanding * (trace_median_price_w / 100)
drop trace_median_price*
gsort cusip year
save "$tmp/amounts_outstanding_y_mv", replace
